存储过程遍历 |
您所在的位置:网站首页 › 达梦数据库insert into › 存储过程遍历 |
需求:达梦数据库,每个省份有对应的一张表,effective_11 ~ effective_65 差不多30多个表,里边存储的企业数据,现在要写一个存储过程,遍历查询这么多表并且求个数,最后以(地区code、数量)的方式插入到一个新表中! 新表字段(id,area_code,valid_num,create_time) AS --声明地区数组 type vcode is table of varchar; areaCodeARR vcode:=vcode('11','12','13','14','15','21','22','23','31','32','33','34','35', '36','37','41','42','43','44','45','46','50','51','52','53','54','61','62','63','64','65'); numSql varchar;--求个数的sql insertSql varchar;-- 各省的添加sql totalSql varchar;-- 合计的sql effNum integer := 0;--各省的有效个数 totalNum integer := 0;--合计 BEGIN for i in 1 .. areaCodeARR.count LOOP -- 求各省的有效个数,并累加给合计 numSql :=' select count(1) from effective_'||areaCodeARR(i)||' where licence_type = 0'; execute immediate numSql into effNum; totalNum := totalNum + effNum; -- 一个省给LICENCE_VALID表添加一条记录 insertSql :='insert into LICENCE_VALID (AREA_CODE,VALID_NUM,CREATE_TIME)'; insertSql := insertSql || ' select * from (select '||areaCodeARR(i)||'0000 as AREA_CODE,count(1) as VALID_NUM,SYSDATE as CREATE_TIME'; insertSql := insertSql || ' from effective_'||areaCodeARR(i)||' where licence_type = 0)'; execute immediate insertSql; end LOOP; -- 最后添加合计 totalSql := 'insert into LICENCE_VALID (AREA_CODE,VALID_NUM,CREATE_TIME) values (''total'','||totalNum||',SYSDATE)'; execute immediate totalSql; commit; END |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |